import pymysql
import xlwt
import datetime
# 数据库操作
config = {
    "host": "1.117.167.225",
    "user": "root",
    "password": "Aa1234zxcv",
    "database": "qc_live",
    "charset": "utf8"
}


# 查询表里所有数据
def xiaolu_incom_task(time):
    conn = pymysql.connect(**config)
    # time = '2023-02-13'
    # time1 = '2023-02-14'

    dt = datetime.datetime.strptime(time, "%Y-%m-%d")
    time1 = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")
    # time = datetime.datetime.now().strftime('%Y-%m-%d')
    # time1 = (datetime.datetime.now() + datetime.timedelta(days=1)).strftime('%Y-%m-%d')

    cursor = conn.cursor()
    tab_list = []
    query = ('select * from t_live_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'" and live_income>0 ORDER BY live_income DESC')
    cursor.execute(query)
    result = cursor.fetchall()
    for (item) in result:
        # print(item)
        my_map = {'uid': item[1], 'income': item[2], 'showincome': item[4], 'video': item[6]}

        # 查询当前直播间所有上榜的用户 上榜人数金额
        # select * from t_live_user_statistics where room_id = 102871 and time >= '2023-02-02'
        query1 = ('select * from t_live_user_statistics where room_id = '+str(item[1])+' and time >= "'+ str(time)+'" and time < "'+str(time1)+'"')
        cursor.execute(query1)
        result1 = cursor.fetchall()
        gx_list = ''
        for (item1) in result1:
            # print('贡献的用户：'+item1[2] + ' 贡献值：'+item1[3])
            gx_list += str(item1[2]) + ' 贡献值：' + str(item1[3]) + '\n'
        my_map['gx_list'] = gx_list

        # 直播间的贡献值分类 直送 背包 type =1 直送  2 背包
        # select * from t_gift_user_statistics where room_id = 104760 and time >= '2023-02-02' order by coin desc
        query2 = ('select * from t_gift_user_statistics where room_id = '+str(item[1])+' and time >= "'+str(time)+'" and time < "'+str(time1)+'" order by coin desc')
        cursor.execute(query2)
        result2 = cursor.fetchall()
        gift_coin = 0
        bg_coin = 0
        for (item2) in result2:
            if item2[4] == 1:
                gift_coin += item2[3]
            else:
                bg_coin += item2[3]
        my_map['gift'] = gift_coin
        my_map['bggift'] = bg_coin

        # 查询直播间当日的直播任务领取金额
        # select SUM(cost) from t_live_task_record where uid = 102871 and time >= '2023-02-01' and time<'2023-02-02'
        query3 = ('select SUM(cost) from t_live_task_record where uid = ' + str(item[1]) + ' and time >= "' + str(
            time) + '" and time < "' + str(time1) + '"')
        cursor.execute(query3)
        result3 = cursor.fetchall()
        live_task = 0
        for (item3) in result3:
            live_task = item3[0]
        my_map['live_task'] = live_task

        query4 = 'select * from t_userinfo where uid =' + str(item[1])
        cursor.execute(query4)
        result4 = cursor.fetchall()
        for (item4) in result4:
            my_map['nick'] = item4[2]

        # 查询当日提现金额
        query5 = 'select money from t_wallet_cash where uid = ' + str(item[1]) + ' and cash_time >= "' + str(
            time) + '" and cash_time < "' + str(time1) + '"'
        cursor.execute(query5)
        result5 = cursor.fetchall()
        payment_money = 0
        for (item5) in result5:
            payment_money = item5[0]
        my_map['payment_money'] = payment_money
        # 添加数据到list里面
        tab_list.append(my_map)



    # 生成报表
    work_book = xlwt.Workbook(encoding='utf-8')
    sheet = work_book.add_sheet('主播贡献榜')
    sheet1 = work_book.add_sheet('主播贡献榜直送')
    sheet.write(0, 0, '用户ID')
    sheet.write(0, 1, '昵称')
    sheet.write(0, 2, '上榜')
    sheet.write(0, 3, '显示的上榜')
    sheet.write(0, 4, '直送礼物金额')
    sheet.write(0, 5, '背包上榜金额')
    sheet.write(0, 6, '直送占比')
    sheet.write(0, 7, '背包占比')
    sheet.write(0, 8, '视频直播小时')
    sheet.write(0, 9, '直播任务领取金额')
    sheet.write(0, 10, '直播贡献列表')
    sheet.write(0, 11, '到账金额')
    sheet.write(0, 12, '提现金额')

    sheet1.write(0, 0, '用户ID')
    sheet1.write(0, 1, '昵称')
    sheet1.write(0, 2, '上榜')
    sheet1.write(0, 3, '显示的上榜')
    sheet1.write(0, 4, '直送礼物金额')
    sheet1.write(0, 5, '背包上榜金额')
    sheet1.write(0, 6, '直送占比')
    sheet1.write(0, 7, '背包占比')
    sheet1.write(0, 8, '视频直播小时')
    sheet1.write(0, 9, '直播任务领取金额')
    sheet1.write(0, 10, '直播贡献列表')
    sheet1.write(0, 11, '到账金额')
    sheet1.write(0, 12, '提现金额')

    index1 = 0
    index2 = 0
    for index in range(len(tab_list)):
        if (tab_list[index]['gift']>0) and (round(tab_list[index]['gift']/tab_list[index]['income'], 4)*100 > 80):
            sheet1.write(index1 + 1, 0, tab_list[index]['uid'])
            sheet1.write(index1 + 1, 1, tab_list[index]['nick'])
            sheet1.write(index1 + 1, 2, tab_list[index]['income'])
            sheet1.write(index1 + 1, 3, tab_list[index]['showincome'])
            sheet1.write(index1 + 1, 4, tab_list[index]['gift'])
            sheet1.write(index1 + 1, 5, tab_list[index]['bggift'])
            sheet1.write(index1 + 1, 6, '0%' if tab_list[index]['gift'] == 0 else str(
                round(tab_list[index]['gift'] / tab_list[index]['income'], 4) * 100) + '%')
            sheet1.write(index1 + 1, 7, '0%' if tab_list[index]['bggift'] == 0 else str(
                round(tab_list[index]['bggift'] / tab_list[index]['income'], 4) * 100) + '%')
            sheet1.write(index1 + 1, 8, round(tab_list[index]['video'] / 60, 1))
            sheet1.write(index1 + 1, 9, tab_list[index]['live_task'])
            sheet1.write(index1 + 1, 10, tab_list[index]['gx_list'])
            try:
                sheet1.write(index1 + 1, 11, (float(tab_list[index]['income']) *0.7 + float(tab_list[index]['live_task']))/1000)
                sheet1.write(index1 + 1, 12, tab_list[index]['payment_money'])
            except Exception as e1:
                sheet1.write(index1 + 1, 11, (float(tab_list[index]['income']) * 0.7 ) / 1000)
                sheet1.write(index1 + 1, 12, tab_list[index]['payment_money'])
                print(e1)
            index1 += 1
        else:
            sheet.write(index2 + 1, 0, tab_list[index]['uid'])
            sheet.write(index2 + 1, 1, tab_list[index]['nick'])
            sheet.write(index2 + 1, 2, tab_list[index]['income'])
            sheet.write(index2 + 1, 3, tab_list[index]['showincome'])
            sheet.write(index2 + 1, 4, tab_list[index]['gift'])
            sheet.write(index2 + 1, 5, tab_list[index]['bggift'])
            sheet.write(index2 + 1, 6, '0%' if tab_list[index]['gift'] == 0 else str(
                round(tab_list[index]['gift'] / tab_list[index]['income'], 4) * 100) + '%')
            sheet.write(index2 + 1, 7, '0%' if tab_list[index]['bggift'] == 0 else str(
                round(tab_list[index]['bggift'] / tab_list[index]['income'], 4) * 100) + '%')
            sheet.write(index2 + 1, 8, round(tab_list[index]['video'] / 60, 1))
            sheet.write(index2 + 1, 9, tab_list[index]['live_task'])
            sheet.write(index2 + 1, 10, tab_list[index]['gx_list'])
            try:
                sheet.write(index2 + 1, 11, (float(tab_list[index]['income'])*0.7 + float(tab_list[index]['live_task'])) / 1000)
                sheet.write(index2 + 1, 12, tab_list[index]['payment_money'])
            except Exception as e1:
                sheet.write(index2 + 1, 11, (float(tab_list[index]['income']) * 0.7) / 1000)
                sheet.write(index2 + 1, 12, tab_list[index]['payment_money'])
                print(e1)
            index2 += 1


    print('统计：')
    sheet2 = work_book.add_sheet('主播贡献榜直送1')
    # 计算当日总上榜金额
    sum_incoin = 0
    for it1 in tab_list:
        sum_incoin += it1['income']
    # 计算当日总任务领取金额
    sum_task = 0
    for it2 in tab_list:
        try:
            if it2['live_task'] != None:
                sum_task += it2['live_task']
        except:
            print('报错' + str(it2['live_task']))
    sheet2.write(0, 1, '当日总榜(元)')
    sheet2.write(0, 2, '当日总任务')
    sheet2.write(0, 4, '幸运礼物上榜（元）')
    sheet2.write(0, 5, '直送上榜（元）')
    sheet2.write(0, 6, '背包上榜（元）')


    sheet2.write(1, 1, sum_incoin/1000)
    sheet2.write(1, 2, sum_task/1000)

    # 幸运礼物总投入产出
    luck_book_total_input = 0
    luck_book_total_output = 0
    query5 = 'select sum(input) as input, sum(output) as output from t_lucky_gift_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'"'
    cursor.execute(query5)
    result5 = cursor.fetchall()
    for (item5) in result5:
        luck_book_total_input = item5[0]
        luck_book_total_output = item5[1]
    sheet2.write(1, 4, luck_book_total_input/10000)

    sheet2.write(3, 4, '幸运礼物投入')
    sheet2.write(3, 5, '幸运礼物产出')
    sheet2.write(3, 6, '幸运礼物盈亏')
    sheet2.write(4, 4, luck_book_total_input)
    sheet2.write(4, 5, luck_book_total_output)
    sheet2.write(4, 6, (luck_book_total_input-luck_book_total_output)/1000 - luck_book_total_input/10000)

    query6 = 'select sum(coin) from t_gift_user_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'" and type = 1'
    cursor.execute(query6)
    result6 = cursor.fetchall()
    for (item6) in result6:
        sheet2.write(1, 5, item6[0]/1000)

    query7 = 'select sum(coin) from t_gift_user_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'" and type = 2'
    cursor.execute(query7)
    result7 = cursor.fetchall()
    for (item7) in result7:
        sheet2.write(1, 6, item7[0]/1000)

    query8 = 'select sum(input) as input, sum(output) from t_game_divination_user_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'" '
    cursor.execute(query8)
    result8 = cursor.fetchall()
    for (item8) in result8:
        sheet2.write(6, 4, '幸运宝典投入')
        sheet2.write(6, 5, '幸运宝典产出')
        sheet2.write(6, 6, '幸运宝典盈亏')
        sheet2.write(7, 4, item8[0]/1000)
        sheet2.write(7, 5, item8[1]/1000)
        sheet2.write(7, 6, (item8[1] - item8[0]) / 1000)

    query9 = 'select sum(input) as input, sum(output) from t_easter_egg_user_statistics where time >= "'+str(time)+'" and time < "'+str(time1)+'" '
    cursor.execute(query9)
    result9 = cursor.fetchall()
    for (item9) in result9:
        sheet2.write(10, 4, '彩蛋投入')
        sheet2.write(10, 5, '彩蛋产出')
        sheet2.write(10, 6, '彩蛋盈亏')
        sheet2.write(11, 4, item9[0])
        sheet2.write(11, 5, item9[1])
        sheet2.write(11, 6, (item9[1] - item9[0]) / 1000)




    # sheet.write(0, 14, '榜上主播分成盈利')
    # sheet.write(0, 15, '流水师傅分成')
    # sheet.write(1, 14, sum_incoin*0.7)
    # sheet.write(1, 15, sum_incoin*0.02)
    work_book.save('C:\\Users\\Administrator\\Documents\\xiaolu\\'+str(time)+'_主播榜单.xls')
    # return tab_list
    cursor.close()
    conn.close()


if __name__ == '__main__':
    # 主播上榜整体情况
    time = datetime.datetime.now().strftime('%Y-%m-%d')
    time = '2023-02-27'
    xiaolu_incom_task(time)
